package check;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBexecute {
    Connection connection;
    private PreparedStatement pstm;
    private ResultSet rs;

    public Connection getConnection() throws Exception {
        try {
            String url = "jdbc:mysql://120.25.152.50:3306/学生成绩管理系统";
            String user = "root";   //这里的信息是需要更改的
            String password = "zqy939597A";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("数据库连接对象" + connection);
            return connection;
        } catch (Exception e) {
            throw new SQLException("驱动错误或连接失败！");
        }
    }

    //查询操作
    public ResultSet executeQurey(String sql,String a) {

        try {
            //得到preparaStatement语句
            pstm = connection.prepareStatement(sql);
            if(a!=null){
                pstm.setString(1,a);
            }
            //执行sql语句
            rs = pstm.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //返回查询结果集
        return rs;
    }

    //插入、删除操作
    public int executeUpdate(String sql, String[] data,String course) {
        int count = 0;
        try {
            pstm = connection.prepareStatement(sql);
            if (data != null) {
                for (int i = 0; i < data.length; i++) {
                    pstm.setString(i + 1, data[i]);
                }

                count = pstm.executeUpdate();
                String sql1 = "update score set course=? where id=?";
                pstm = connection.prepareStatement(sql1);
                System.out.println("!!!"+course);
                System.out.println("!!!"+data[0]);
                pstm.setString(1,course);
                pstm.setString(2,data[0]);
                pstm.executeUpdate();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        //返回修改成功的行数
        return count;
    }
    public int executeDelete(String sql, String[] data) {
        int count = 0;
        try {
            pstm = connection.prepareStatement(sql);
            if (data != null) {
                for (int i = 0; i < data.length; i++) {
                    pstm.setString(i + 1, data[i]);
                }
                count = pstm.executeUpdate();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        //返回修改成功的行数
        return count;
    }

    public void closeAll() {
        // 如果rs不空，关闭rs
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 如果pstm不空，关闭pstm
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 如果conn不空，关闭conn
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}


